package cn.doways.api.mapper;

import cn.doways.api.dto.*;
import cn.doways.api.entity.Secret;
import cn.doways.api.entity.Student;
import cn.doways.api.entity.StudentCollegeDetail;
import cn.doways.api.entity.WeekTime;
import cn.doways.api.vo.LateListVO;
import cn.doways.api.vo.PersonalCourseTableVO;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * @ClassName StudentMapper
 * @Description //TODO
 * @Author shellming
 * @Date 2019-08-08 17:33
 **/

@Repository
public interface UsersMapper {

    @Select("SELECT student.grade,college.`name` college,major.`name` major,`level`.`name` level,student.serial FROM secret,student,college,major,`level` WHERE student.collegeId=college.id AND student.major=major.id AND student.`level`=`level`.id AND secret.id=student.id AND secret.phoneNum=#{phoneNum}")
    @ResultType(StudentCollegeDetail.class)
    StudentCollegeDetail getStudentDetailInfo(String phoneNum);

    @Select("SELECT student.id,`name`,sex,avatar,nation,birthday,introduction,politics FROM student,secret WHERE secret.id=student.id AND secret.phoneNum=#{phoneNum}")
    Userinfo getUserInfo(String phoneNum);

    @Select("SELECT name from student where id=#{id}")
    @ResultType(Student.class)
    Student getNameById(String id);

    @Select("SELECT COUNT(phoneNum) FROM secret WHERE id=#{id}")
    @ResultType(Integer.class)
    int hasStudent(String id);

    @Insert("INSERT INTO secret (phoneNum,id,activeStatus,passwordHash) VALUES (#{phoneNumber},#{id},#{activeStatus},#{passwordHash})")
    @ResultType(Integer.class)
    int saveStudent(Secret secret);

    Secret authStudent(String phoneNumber);

    @Update("UPDATE student SET introduction=#{intro} where id=#{id}")
    @ResultType(Integer.class)
    int updateIntro(String id,String intro);

    @Update("UPDATE student SET avatar=#{avatarUrl} WHERE id=#{id}")
    @ResultType(Integer.class)
    int updateAvatar(String id,String avatarUrl);

    @Select("SELECT courseTable.timeId,building.buildingName,courseTable.floorsMajor,courseTable.roomMinor,course.courseName,teacher.NAME,classtime.startTime,classtime.endTime FROM courseTable,building,classtime,course,teacher WHERE courseTable.majorId=#{major} AND grade=#{grade} AND classId=#{classId} AND WEEK=#{week} AND courseTable.buildingUuid=building.buildingUuid AND classtime.timeid=courseTable.timeId AND course.majorId=courseTable.majorId AND course.courseId=courseTable.courseId AND courseTable.teacherId=teacher.id AND #{justWeek}>=courseTable.startweek AND #{justWeek}<=courseTable.endweek ORDER BY courseTable.timeId ASC")
    @ResultType(PersonalCourseTableVO.class)
    List<PersonalCourseTableVO> getPersonalCourseTable(int grade,int week,int major,int classId,int justWeek);

    @Select("SELECT student.classId,student.major,student.grade FROM student WHERE student.id=#{id}")
    @ResultType(Student.class)
    Student getMajorClassidAndGrade(String id);

    @Select("SELECT MAX(courseTable.endweek) AS maxWeek FROM courseTable WHERE courseTable.majorId=#{major} AND grade=#{grade} AND classId=#{classId}")
    @ResultType(Integer.class)
    int getMaxWeek(int grade,int major,int classId);

    @Select("SELECT*FROM weekTime WHERE weekTime.id=#{id} AND weekTime.one=#{yearOne} AND weekTime.two=#{yearTwo}")
    @ResultType(WeekTime.class)
    WeekTime getWeekTime(int id,int yearOne,int yearTwo);

    @Select("SELECT courseTable.timeId,classtime.startTime,classtime.endTime,courseTable.buildingUuid,courseTable.floorsMajor,courseTable.roomMinor,courseTable.startweek,courseTable.endweek FROM courseTable,classtime,course,teacher WHERE courseTable.majorId=#{majorId} AND grade=#{grade} AND classId=#{classId} AND WEEK=#{week} AND classtime.timeid=courseTable.timeId AND course.majorId=courseTable.majorId AND course.courseId=courseTable.courseId AND courseTable.teacherId=teacher.id AND courseTable.timeId=#{timeId}")
    @ResultType(SignCourseDTO.class)
    SignCourseDTO getSignCourse(int majorId,int grade,int classId,int week,int timeId);

    @Select("SELECT courseTable.grade,courseTable.majorId,courseTable.classId,courseTable.courseId,courseTable.`week`,courseTable.teacherId,courseTable.timeId,courseTable.buildingUuid,courseTable.floorsMajor,courseTable.roomMinor FROM courseTable,classtime,course,teacher WHERE courseTable.majorId=#{majorId} AND grade=#{grade} AND classId=#{classId} AND WEEK=#{week} AND classtime.timeid=courseTable.timeId AND course.majorId=courseTable.majorId AND course.courseId=courseTable.courseId AND courseTable.teacherId=teacher.id AND courseTable.timeId=#{timeId}")
    @ResultType(SaveSignDTO.class)
    SaveSignDTO getSaveSign(int majorId,int grade,int classId,int week,int timeId);

    @Select("SELECT id FROM student WHERE grade=#{grade} AND major=#{major} AND classId=#{classId}")
    @ResultType(SignStudentDTO.class)
    List<SignStudentDTO> getClassStudent(int grade,int major,int classId);

    @Select("SELECT courseTable.timeId FROM courseTable WHERE courseTable.majorId=#{major} AND grade=#{grade} AND classId=#{classId} AND WEEK=#{week} AND #{justWeek}>=courseTable.startweek AND #{justWeek}<=courseTable.endweek ORDER BY courseTable.timeId ASC")
    @ResultType(String.class)
    List<String>getTodayClassId(int grade,int major,int classId,int week,int justWeek);

    @Select("SELECT `leave`.startNumber,`leave`.endNumber FROM `leave` WHERE `leave`.leaveDate=#{date} AND `leave`.studentId=#{id}")
    @ResultType(LeaveCheckDTO.class)
    List<LeaveCheckDTO> getLeaveCheck(String id, Date date);

    @Insert("INSERT INTO `doways`.`leave` (`id`,`studentId`,`grade`,`classId`,`majorId`,`timestamp`,`leaveDate`,`leaveReason`,`startNumber`,`endNumber`,`reasonContent`,`imageUrl`) VALUES (UUID_SHORT(),#{studentId},#{grade},#{classId},#{majorId},#{timestamp},#{leaveDate},#{leaveReason},#{startNumber},#{endNumber},#{leaveContent},#{imageUrl});")
    @ResultType(Integer.class)
    int doLeaveApplication(LeaveRequestDTO leaveRequestDTO);

    @Select("SELECT studentId,TIMESTAMP,leaveDate,leaveReason.content AS leaveReason,startNumber,endNumber FROM `leave`,leaveReason WHERE `leave`.studentId=#{id} AND leaveReason.id=`leave`.leaveReason")
    @ResultType(LateListVO.class)
    List<LateListVO> getLateList(String id);

    @Select("SELECT leaveReason,startNumber,endNumber FROM `leave` WHERE `leave`.studentId=#{id} AND `leave`.leaveDate=#{date}")
    @ResultType(GetLeaveForSign.class)
    List<GetLeaveForSign> getLeaveForSign(String id,Date date);
}
